Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / mrim002.arc / MICRORIM.002

Wrap

Text File | 1986-12-02 | 52.5 KB | 1,387 lines

Volume 1, Number 2 November 1986 :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: :: *************** :: :: ********************* :: :: *************************** :: :: ****************************** :: :: **************** ************ :: :: *************** ************* :: :: *************** ************** :: :: *************** *************** :: :: **************** ***************** :: :: ***************** ************* :: :: ***************** ************** Microrim ONLINE :: :: ****************** *************** """"""""""""""" :: :: ****************** **************** Online Technical News :: :: ****************** ***************** :: :: ****************** ****************** :: :: ****************** ******************* :: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: Technical tips, techniques, and announcements from Microrim, Inc. Microrim ONLINE is published electronically approximately every month by Microrim, Inc. and distributed exclusively on the Microrim Bulletin Board System (BBS). You can obtain your copy free of charge by downloading it from the TECH-ED area of the FILES section. (206) 881- 8119. Setup: 8 data bits, 1 stop bit, No parity, and 300 or 1200 baud; operating 24 hours, seven days a week. COPYRIGHT """"""""" Copyright (c) 1986 by Microrim, Inc. All rights reserved. Microrim, Inc. authorizes the free distribution of this document for educational purposes as long as no charge is made and this document is distributed exactly as is without modification. Toward this end, this document may be stored in, uploaded to, and downloaded from any Bulletin Board Service (BBS) or electronic information service as long as no charge is made. CONTRIBUTIONS """"""""""""" You are encouraged to contribute to MICRORIM ONLINE. Please upload your article, application, or application story to the Microrim BBS or send an IBM compatible disk in standard ASCII format to: Kay D. Dayss, Microrim ONLINE Editor Microrim, Inc. 3925 159th Ave. N.E. P.O. Box 97022 Redmond, WA 98073-9722 By submitting an article or application to MICRORIM ONLINE, you agree that the material is not confidential and that Microrim, Inc., may use, duplicate, modify, publish, or sell it without obligation or liability to you or anyone else. MICRORIM ONLINE November 1986 ------------------------------- Page 1 TRADEMARKS """""""""" R:BASE is a registered trademark of Microrim, Inc. MICRORIM is a registered trademark of Microrim, Inc. XRW is a trademark of Microrim, Inc. IBM is a registered trademark of International Business Machines Corp. XT and AT are trademarks of International Business Machines Corp. DISCLAIMER """""""""" Microrim, Inc., makes no representation or warranties with respect to the contents hereof, and specifically disclaims any implied warranties of merchantability or fitness for any particular purpose. Further, Microrim, Inc., reserves the right to revise this publication and to make changes in the content hereof without obligation to notify any person of such revision or change and shall not be liable for errors contained herein or for incidental or consequential damages in connection with the furnishing, performance, or use of this material. All opinions and product reviews in MICRORIM ONLINE are those of the author and not necessarily those of Microrim, Inc. :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: :: TABLE OF CONTENTS :: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: Text Results and Nulls in Logical IFEQ, IFLT, and IFGT Functions...... Tracking Leads and Customers.......................................... Printing Columnar Aging Reports....................................... Printing a Report Sorted by a Column in a Lookup Table................ Data Entry and Output Formats, Masks, or "Pictures" .................. Printing Columnar Reports - Multiple Across Labels.................... ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: MICRORIM ONLINE November 1986 ------------------------------- Page 2 :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: :: TECHNICAL NOTES :: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" TEXT RESULTS AND NULLS IN LOGICAL IFEQ, IFLT, AND IFGT FUNCTIONS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-1 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : FUNCTIONS SUBCATEGORY : TEXT/NULLS IN IF """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" The logical functions IFEQ, IFGT, and IFLT will not allow text arguments, but I want to set my variable to a text value using these functions. For example, if a value is in a low range, I want to print an L; if it is in a middle range, an M; and if high, an H. The IF functions are great for dealing with numbers, but I want the result of the function to be the text letters L, M, or H. EXPLANATION: """"""""""" Use a combination of functions. Instead of using the text character, use its ASCII code equivalent and then use the CHAR function to convert it to its text equivalent. SOLUTION: """""""" To solve the specific example of wanting to print a letter to represent a range of values, follow these steps: Step One: Determine the ranges Determine the high and low values for each of the letters to be printed and write them down. Use only the greater than (GT), equal to (EQ), and less than (LT) operators because operators like GE (for greater than and equal to) are not supported by the IF functions. For example: HIGH = GT 99 *( FOR A RANGE OF 100 TO INFINITY ) MIDDLE = GT 49 AND LT 100 *( FOR A RANGE OF 50 TO 99 ) LOW = LT 50 *( FOR A RANGE UP TO 49 ) Step Two: Look up the ASCII codes Next, look up the ASCII codes for the letters you want to print. An ASCII chart is in your USER'S MANUAL on page 10-21. For example, the capital letter H is ASCII code 72, M is 77, and L is 76. MICRORIM ONLINE November 1986 ------------------------------- Page 3 Step Three: Write the necessary expressions. Finally, write the expressions you need. For example the following command file will look at the last row in a table named RANGE and show an H if the value is in the high range, an M if it is in the medium range, or an L if it is low. This command file assumes that the table holding the data is named RANGE and the numeric column is named NUM. CLEAR ALL VAR SET VAR VHIGH = 99 *( this is the lowest high number minus one ) SET VAR VLOW = 50 *( this is the highest low number plus one ) SET VAR VNUM TO NUM IN RANGE WHERE COUNT = LAST SET VAR VCODE = (IFGT(.VNUM,.VHIGH,72,(IFLT(.VNUM,.VLOW,76,77)))) SET VAR VLETTER TO (CHAR(.VCODE)) SHOW VAR VLETTER Similar expressions can be used within the report writer to create reports that print letters instead of numbers for ranges. See the technical note on Charting for a practical application of this. If you wanted to print a blank if the column had a NULL value and H, M, or L if a value exists, you would slightly revise the above code to do this. First you would test for the existence of a value. If the column is NULL, a blank is printed. Existence is tested differently depending on whether you have zero set on or off. If zero is set off (which is the default for R:BASE when no database is open), you would set a VDECIDE variable to one (1) if the value is greater than, less than, or equal to zero (0). If none of these conditions is true, the value is NULL and the VDECIDE variable is set to zero (0). A blank is represented by an ASCII code of 32 so that is what is used if VDECIDE is equal to zero. Here is the modified code to use to check for a NULL value when zero is set off: MICRORIM ONLINE November 1986 ------------------------------- Page 4 *( ZERO-OFF.CMD ) SET ZERO OFF CLEAR ALL VAR SET VAR VHIGH = 99 SET VAR VLOW = 50 SET VAR VNUM TO NUM IN RANGE WHERE COUNT = LAST SET VAR VDECIDE TO (IFGT(.VNUM,0,1,(IFLT(.VNUM,0,1,(IFEQ(.VNUM,0,1,0)))))) SET VAR VCODE = (IFEQ(.VDECIDE,1,(IFGT(.VNUM,.VHIGH,72,(IFLT(.VNUM,.VLOW,76,+ 77)))),32)) SET VAR VLETTER TO (CHAR(.VCODE)) SHOW VAR VLETTER If zero is set on, you would set a VDECIDE variable to zero (0) if the value is equal to "" (quote quote) and otherwise set it to one (1). When zero is set on, a NULL value is equal to "" (quote quote) in any expression. Use the following modified code to check for a NULL value (in an expression) when zero is set on: *( ZERO-ON.CMD ) SET ZERO ON CLEAR ALL VAR SET VAR VHIGH = 99 SET VAR VLOW = 50 SET VAR VNUM TO NUM IN RANGE WHERE COUNT = LAST SET VAR VDECIDE TO (IFEQ(.VNUM,"",0,1)) SET VAR VCODE = (IFEQ(.VDECIDE,1,(IFGT(.VNUM,.VHIGH,72,(IFLT(.VNUM,.VLOW,76,+ 77)))),32)) SET VAR VLETTER TO (CHAR(.VCODE)) SHOW VAR VLETTER Be careful to use the code appropriate for your particular zero setting. If you have zero set on and you use ZERO-OFF.CMD (without the SET ZERO command in it) then you will have all your zero values printed as blanks as well as the NULL values. If you have zero set off and you use ZERO-ON.CMD (without the SET ZERO command in it) then you will get error messages similar to the following: -ERROR- IFEQ does not allow TEXT arguments. -ERROR- Expression cannot be evaluated The methods described in this Technical Note can also be used to test single-character text values in the logical IF functions by using this same technique. The logical IF functions will only accept numeric data so all you have to do is first use the ICHAR function to change the ASCII letter to its numeric ASCII code. Now test the code in the logical function. Then, if need be, you can use the CHAR function to set the numeric ASCII number back to the text letter it represents. MICRORIM ONLINE November 1986 ------------------------------- Page 5 """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" TRACKING LEADS AND CUSTOMERS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-2 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : APPLICATIONS SUBCATEGORY : TRACKING """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" I have two tables PEOPLE and ORDERS that are linked together with a column named ID. One has all the people in it and the other has all the orders in it. If there is an order for a particular person, then that person is a customer. Otherwise, he or she is a lead. Obviously this process is dynamic. As orders come in, leads are turning into customers. I want to know, by looking at the PEOPLE table, who is a lead and who is a customer. In addition, if that person is a customer, I want to know when the last order date was. EXPLANATION: """"""""""" It is possible to automate the process of determining who is a lead and who is a customer by setting up a simple two-column form for data entry of orders that will automatically update a LASTORD column on the PEOPLE table with the current order date. In this example, you want to know at a glance who is a lead and who is a customer. In addition, if the person is a customer, you want to know the last order date. ID is linking column. You can do this by using a two table data entry form and using an expression in the form to set LASTORD in PEOPLE to the system date. The form will be used every time a new order is entered. SOLUTION: """""""" Include the following two steps in the design of your application: Step One: Add a date column named LASTORD to the PEOPLE table. LASTORD will hold the last order date. If LASTORD has no date in it, then that person is a lead because they have never ordered any product. Step Two: Define a data entry form named ORDERS to be used to enter all orders into the ORDERS table. The ORDERS form needs to have both of the two tables associated with it, the PEOPLE table as the first table and the ORDERS table as the second table. Define a master lookup expression for the PEOPLE table that will bring up the columns in the PEOPLE MICRORIM ONLINE November 1986 ------------------------------- Page 6 table based on the ID column value entered by the operator. The following is an example: LASTNAME = LASTNAME IN PEOPLE WHERE ID = ID Master lookups allow you to edit data already in the PEOPLE table when entering new orders into the ORDERS table. For more information on master lookups, see the LOOKING UP VALUES IN TABLES section beginning on page 4-34 of your R:BASE System V User's Manual. In addition to the master lookup, add another expression for the PEOPLE table portion of the form that looks like this: LASTORD = .#DATE Now, when a new order is entered, the ORDERS data entry form will automatically look up the person in the PEOPLE table and assign the system date to the LASTORD column in the PEOPLE table. Use the ORDERS form with the ENTER command whenever you have new orders to be entered: ENTER ORDERS When adding new people to the PEOPLE table, use a different form that leaves the LASTORD column null. Now, whenever you are looking at a particular row in the PEOPLE table, you can tell at a glance whether or not the person is a lead or a customer. If LASTORD contains a date, then that person is a customer and the date is the last day the customer placed an order. If no date appears, the person is a lead who has never placed an order. """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PRINTING COLUMNAR AGING REPORTS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-3 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : REPORTS SUBCATEGORY : AGING/COLUMNAR REPS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" I want to print a columnar aging report. In my aging report I am printing columns of information and I want the report to decide which report column to use when printing a piece of data. I want five report columns for each of the age groupings: current, 30 to 60 days old, 60 to 90 days old, 90 to 120 days old, and over 120 days old. MICRORIM ONLINE November 1986 ------------------------------- Page 7 EXPLANATION: """"""""""" Columnar reports are reports where several rows from an R:BASE table are printed horizontally across the page into different report columns before moving vertically to the next row in the report. In columnar reports, the report writer must decide which report column to use. The decision can be based on the value of the item or on the position of the row. An aging type of report is an example of a kind of columnar report where the decision is based on the number of days that have passed between a date stored in a date column and the current system date. An example is a five-column aged accounts receivable report. This report has a line for each account and shows the age ranges for all the dollars that are due from that account. Each report column in the report holds a range of ages. With an accounts receivable report, for example, the following inclusive age ranges are often used: 0-29 days old (or current), 30-59 days old, 60-89 days old, 90-120 days old, and over 120 days old. Blanks are printed wherever there are no dollars due in that age range. Three other types of columnar reports (by month, ranges, and multiple across labels) will be covered in other Microrim Technical Notes. Here is a synopsis of each of these three types: BY MONTH TYPE OF REPORT: Here the decision about which report column to use is based on the month value in a date column. In the monthly report, there is no comparison to another date as there is in the aging report. A sales report by part by calendar month is a good example of this type of report. This report is a report with a line for each part number and 14 columns. The 14 columns include a column for the part number, one for each month of the year, and the one for total for the year. The total quantity for each month (for each part number) is added up and printed in the appropriate position in the report. If no parts for a particular part number are sold in a particular month, then that "cell" of the report is left blank. RANGES TYPE OF REPORT: Here the report writer looks at a database column that contains numbers and compares it to ranges that are located in each of the report columns. The decision about where to print the value depends on a comparison between the value and the applicable range(s). Often a range report will print a letter in the report columns to indicate what range ([H]igh, [L]ow, or [N]ormal) the number falls under for that particular report column The range type of report is often used in medical, statistical, or scientific applications. A good example is a chemical scan report. This report has a different normal range for each of the chemicals. Each chemical has its own report column on the top of the page and each line in the report represents a different sample. Each chemical has its own range of normal values. The report writer prints an H in the cell of the report if the numeric value for that sample is in the high range for that chemical, an L if the value is low, and an N if the value is in the normal range. If the sample has not been tested for a particular chemical, a blank is printed. MICRORIM ONLINE November 1986 ------------------------------- Page 8 MULTI-ACROSS LABELS: For example, three-across labels. This kind of report has rows of data printed across a page in columns. Here the decision is based on the position of the row in the table or in the sort order. The decision in this case has nothing to do with the value in a column. In a three-across situation, for example, the first three rows are printed on the first line of the report, the next three rows on the second line, and so forth. SOLUTION: """""""" Sometimes people will look at a columnar report and attempt to design the database to match the report. This is not the best approach to take. Report column names are not the same thing as database column names. When designing your database, avoid including data in the column name. For example, the chemical scan report should NOT have a database where every chemical is included as a separate column name. Think of the headaches this causes over time as the chemicals change. Just as you would not have a column for every different customer name, do not make a column for every chemical, month, range, etc. Although these items are report column names on your report, they are not appropriate as database column names. Instead use a generic column name such as CHEMID for chemical identification code. An aging report takes a set of accounts and ages the amount that is due from or to each. Normally, a business will print an aging report on accounts receivable or on accounts payable. This kind of report adds up dollar amounts for an account that fall within a certain age range. The following is a typical aging report: AGED ACCOUNTS RECEIVABLE REPORT ACCOUNT CURRENT 30 TO 60 60 TO 90 90 TO 120 OVER 120 NUMBER DAYS OLD DAYS OLD DAYS OLD DAYS OLD ======= ======= ======== ======== ========= ======== 101 $178.85 $73.00 $146.00 102 $324.85 $146.00 $146.00 $146.00 103 $470.85 $219.00 $219.00 $219.00 104 $908.85 $292.00 $292.00 ======= ======== ======== ========= ======== TOTALS: $1,379.70 $835.85 $543.85 $511.00 $511.00 From this report, you can see at a glance which accounts have owed you the most money for the longest time. MICRORIM ONLINE November 1986 ------------------------------- Page 9 To produce this report follow these steps: STEP ONE: Define the database. This example uses a table named ACCTS with these columns: ACCTNUM INTEGER *( the account number ) AMOUNT CURRENCY TDATE DATE *( the transaction date) Next, you will want to load the table with data. For this example, the ACCTS table has the following data: ACCTNUM AMOUNT TDATE ---------- --------------- -------- 101 $73.00 05/20/86 102 $146.00 06/19/86 103 $219.00 07/15/86 104 $292.00 08/13/86 101 $73.00 07/05/86 102 $146.00 08/04/86 103 $219.00 08/30/86 104 $292.00 09/28/86 101 $73.00 07/28/86 102 $146.00 08/27/86 103 $219.00 09/22/86 104 $292.00 10/21/86 101 $73.00 08/29/86 102 $146.00 09/28/86 103 $219.00 10/24/86 104 $292.00 11/10/86 101 $105.85 08/29/86 102 $178.85 09/28/86 103 $251.85 10/24/86 104 $324.85 11/03/86 STEP TWO: Define and initialize the totalers you will need Define a variable VBLANK as CURRENCY it will be set to a null value in the report expressions list later with the expression: VBLANK = "" Next, define and initialize all the variables that will be used as accumulators. An accumulator is a a variable that grows in size as quantities are added to it. For example: VTOTAL = .VTOTAL + .VADDTHIS Here, VTOTAL is an accumulator because an amount is being added to its current value. Notice how the name of the accumulator variable VTOTAL (in the above example) appears on both the left and the right of the equal sign. You will need an accumulator variable for each cell in each of the five ages in the report. This is because you will need to hold onto five totals and then print. In this aging report, break totals are MICRORIM ONLINE November 1986 ------------------------------- Page 10 printing in each of the cells and a grand total is printing on the TOTALS line. Therefore you will need ten variables; one for each of the break totals (for each of the five ages in the report) and one for each of the grand totals (for each of the five ages). For the example aging report in this Technical Note, use the following list of commands to do the necessary initializations: CLEAR ALL VAR SET VAR VBLANK CURRENCY SET VAR V1TOT TO $0.0 SET VAR V2TOT TO $0.0 SET VAR V3TOT TO $0.0 SET VAR V4TOT TO $0.0 SET VAR V5TOT TO $0.0 SET VAR V1GRAND TO $0.0 SET VAR V2GRAND TO $0.0 SET VAR V3GRAND TO $0.0 SET VAR V4GRAND TO $0.0 SET VAR V5GRAND TO $0.0 STEP THREE: Define the report using Reports EXPRESS When prompted for the name of the report, enter AGING. Give ACCTS as the name of the table. Choose Expressions from the Reports Definition Menu and set up the expressions listed below. This report uses four sets of variables. The first set of variables: VCURRENT, V30TO60, V60TO90, V90TO120, and VOVER120 are used to hold the value in the current row. The second set of variables: V1TOT, V2TOT, V3TOT, V4TOT, and V5TOT are the accumulators for each of the five report columns for each row. Remember that the report columns being accumulated are: CURRENT (0 to 29 days old), 30-60 days old, 60- 90 days old, 90-120 days old, and over 120 days old. Each of the five totals are reset at break time to zero (0). A third set of variables: V1PRINT, V2PRINT, V3PRINT, V4PRINT, and V5PRINT is used to print the totals before they too are reset to zero at break time. These print variables are necessary in order to prevent the printing of zeros on the report. The print variables are set to a null value if the value of the accumulator is zero. In addition, you need to SET ZERO OFF and SET NULL " " before printing the report to guarantee that the zeros on the report will print as blanks. MICRORIM ONLINE November 1986 ------------------------------- Page 11 The fourth and final set of variables: V1GRAND, V2GRAND, V3GRAND, V4GRAND, and V5GRAND are used to accumulate the grand totals. The grand totals are not reset at break time, they continue to accumulate the amounts for the five report columns. 1: INTEGER : VAGE = (.#DATE - 'TDATE') 2: CURRENCY: VCURRENT = (IFLT(.VAGE,30,'AMOUNT',0)) 3: CURRENCY: V30TO60 = (IFEQ(.VAGE,30,'AMOUNT',(IFGT(.VAGE,30,(IF LT(.VAGE,60,'AMOUNT',0)),0)))) 4: CURRENCY: V60TO90 = (IFEQ(.VAGE,60,'AMOUNT',(IFGT(.VAGE,60,(IF LT(.VAGE,90,'AMOUNT',0)),0)))) 5: CURRENCY: V90TO120 = (IFEQ(.VAGE,90,'AMOUNT',(IFGT(.VAGE,90,(IF LT(.VAGE,120,'AMOUNT',0)),0)))) 6: CURRENCY: VOVER120 = (IFEQ(.VAGE,120,'AMOUNT',(IFGT(.VAGE,120,' AMOUNT',0)))) 7: CURRENCY: VBLANK = "" 8: CURRENCY: V1TOT = (.V1TOT + .VCURRENT) 9: CURRENCY: V2TOT = (.V2TOT + .V30TO60) 10: CURRENCY: V3TOT = (.V3TOT + .V60TO90) 11: CURRENCY: V4TOT = (.V4TOT + .V90TO120) 12: CURRENCY: V5TOT = (.V5TOT + .VOVER120) 13: CURRENCY: V1PRINT = (IFEQ(.V1TOT,0,.VBLANK,.V1TOT)) 14: CURRENCY: V2PRINT = (IFEQ(.V2TOT,0,.VBLANK,.V2TOT)) 15: CURRENCY: V3PRINT = (IFEQ(.V3TOT,0,.VBLANK,.V3TOT)) 16: CURRENCY: V4PRINT = (IFEQ(.V4TOT,0,.VBLANK,.V4TOT)) 17: CURRENCY: V5PRINT = (IFEQ(.V5TOT,0,.VBLANK,.V5TOT)) 18: CURRENCY: V1GRAND = (.V1GRAND + .VCURRENT) 19: CURRENCY: V2GRAND = (.V2GRAND + .V30TO60) 20: CURRENCY: V3GRAND = (.V3GRAND + .V60TO90) 21: CURRENCY: V4GRAND = (.V4GRAND + .V90TO120) 22: CURRENCY: V5GRAND = (.V5GRAND + .VOVER120) After setting up the expressions, press the [ESC] key to return to the Reports Definition Menu and choose Configure. Set up ACCTNUM as the break column, press [Y] for the variable resets, and add the following variables to the reset list: V1TOT V2TOT V3TOT V4TOT V5TOT V1PRINT V2PRINT V3PRINT V4PRINT V5PRINT When finished, the top of the configure menu will look like this: Lines Per Page .................: 60 Remove Initial Carriage Return..: [NO ] Manual Break Reset .............: [NO ] Page Footer Line Number.........: 0 BREAKPOINTS FORM FEEDS Break Variable Header Footer Column Reset Before After Before After -------- ------ ------ ------ ------ ------ Report [NO ] [NO ] [NO ] [NO ] Page [NO ] Break1 ACCTNUM [YES] [NO ] Finally, press the [ESC] key to get back to the Reports Definition MICRORIM ONLINE November 1986 ------------------------------- Page 12 Menu and choose Edit. Mark, edit, and locate so that the report looks like the one listed below. Note that the field locations on the F1 line are the following columns or variables in the following order: ACCTNUM V1PRINT V2PRINT V3PRINT V4PRINT V5PRINT The field locations on the RF line are: V1GRAND V2GRAND V3GRAND V4GRAND V5GRAND RH AGED ACCOUNTS RECEIVABLE REPORT RH RH ACCOUNT CURRENT 30 TO 60 60 TO 90 90 TO 120 OVER 120 RH NUMBER DAYS OLD DAYS OLD DAYS OLD DAYS OLD RH ======= ======= ======== ======== ========= ======== F1 S E S E S E S E S E S E RF ======= ======== ======== ========= ======== RF TOTALS: S E S E S E S E S E STEP FOUR: Print the report Now you can print the report by running a command file that clears all the variables used in the report, initializes all variables that are used for accumulating totals, sets zero off (to keep zeros from printing on the report), sets null to a blank, prints the report (with any SORTED BY or WHERE clause that you need), and then sets null back to -0-. The following command file, for example, would work for this sample aging report: CLEAR ALL VAR SET VAR V1TOT TO $0.0 SET VAR V2TOT TO $0.0 SET VAR V3TOT TO $0.0 SET VAR V4TOT TO $0.0 SET VAR V5TOT TO $0.0 SET VAR V1GRAND TO $0.0 SET VAR V2GRAND TO $0.0 SET VAR V3GRAND TO $0.0 SET VAR V4GRAND TO $0.0 SET VAR V5GRAND TO $0.0 SET VAR VBLANK CURRENCY SET ZERO OFF SET NULL " " PRINT AGING SET NULL -0- MICRORIM ONLINE November 1986 ------------------------------- Page 13 """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PRINTING A REPORT SORTED BY A COLUMN IN A LOOKUP TABLE """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-4 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 R:BASE 5000 1.01 and higher CATEGORY : REPORTS SUBCATEGORY : VIEWS AND SORTING """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" My application has all the customers and their address information in one table and all the transactions in another table. The two tables are related by the customer number column CUSTID. I want to print labels (or a report) sorted by the zip code and I want to print only those customers who meet certain selection criteria in the transaction table. The transaction table is the base table. The address information in the customer table is looked up. What is the best way to accomplish this sorting in R:BASE 5000? In R:BASE System V? SOLUTION: """""""" It is a good design strategy to separate the information into two tables as you have done. Therefore, keep the good design you have and use one of the following methods: To sort on a column in the lookup table in R:BASE System V Define a VIEW using the VIEW command that contains all the columns that you need for your report as well as the linking identification column. Print the report based on the VIEW. In other words, when you are asked for the name of the table to associate with the report, give the name of your VIEW instead. Then, when ready to print the report, issue the following command: PRINT reptname SORTED BY ZIP Views are very useful for situations like this. Views can be used in reports and will work instead of a table name with all of the following commands: PRINT SELECT LIST VIEWS RENAME VIEW RPW COMPUTE CROSSTAB REMOVE VIEW REPORTS MPW To sort on a column in the look up table in R:BASE 5000 ■ Include the zipcode as the first part of the identification column number and then print the report sorted on the id column. This would insure that the lookups are done in the correct order. OR ■ Intersect the two tables and print the report based on the resulting table. MICRORIM ONLINE November 1986 ------------------------------- Page 14 """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATA ENTRY AND OUTPUT FORMATS, MASKS, OR "PICTURES" """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-5 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : FORMS/REPORTS SUBCATEGORY : FORMATS, MASKS & "PICTURES" """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" I want special formats in my forms and reports. Some people refer to these as masks or "pictures". For example, when entering phone numbers, it would be nice to have the number look like this during data entry: (312) 555-1212 However, I want to store the number in the database like this: 3125551212 EXPLANATION: """"""""""" R:BASE allows many different format options in both forms and reports by using variables, functions, and concatenation schemes. SOLUTION: """""""" Store the item in a text column and you can use variables, functions, and concatenation to manipulate the display of the data in different "picture" formats. For the phone number example, use the following steps for data entry into forms according to the format you want: ■ Enter the text that you want in edit mode: ( ) - ■ Locate three variables: VAREA for the area code, VPRE for the prefix, and VLAST for the last four characters. Define all three variables as TEXT data type. (S E) S E-S E | | | VAREA VPRE VLAST ■ Set autoskip on before doing data entry using the form and the cursor will automatically skip from the area code to the prefix to MICRORIM ONLINE November 1986 ------------------------------- Page 15 the last four digits. ■ First, make sure that your PHONE column is defined as TEXT data type. Then, in the expression list in forms, include the following expression to make sure that the column PHONE is loaded with only the numbers: PHONE = (.VAREA + .VPRE + .VLAST) For display of the number in reports, use the SGET function to pull the string apart into variables and locate the variables on the report. For example the following expressions in a report will recreate the three variables VAREA, VPRE, and VLAST: VAREA = (SGET('PHONE',3,1)) VPRE = (SGET('PHONE',3,4)) VLAST = (SGET('PHONE',4,7)) Now, all you need to do is edit in the parentheses and locate the variables just as was done in the form: (S E) S E-S E | | | VAREA VPRE VLAST Using these powerful R:BASE features, you can create specialized formats or "pictures" of your data to meet your needs. """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PRINTING COLUMNAR REPORTS - MULTIPLE ACROSS LABELS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DATE : 10/86 NUMBER : EX-10-6 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : REPORTS SUBCATEGORY : MULTI-UP LABELS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" DESCRIPTION: """"""""""" I want to print multiple across labels. EXPLANATION: """"""""""" Columnar reports, such as multi-across labels, are reports where several rows from an R:BASE table are printed horizontally across the page into different report columns before moving to the next row in the report. In columnar reports, the report writer must decide which report column to use. The decision can be based on the value of the item or on the position of the row. In a multiple across labels report (for example, a three-across labels report) the decision about which report column to use is based on the MICRORIM ONLINE November 1986 ------------------------------- Page 16 position of the row in the table or the position of the row in the sort order. The decision in this case has nothing to do with the value in a column. In a three-across situation, for example, the first three rows are printed on the first line of the report, the next three rows on the second line, and so forth. Three other types of columnar reports (aging report, monthly report, and ranges report) will be covered in other Microrim Technical Notes. In each of these other types, it is the value in a column that determines which report column to use. Here is a synopsis of each of these other three types: AGING TYPE OF REPORT: In this type of columnar report, the decision is based on the number of days that have passed between a date stored in a date column and the current system date. An example is a five- column aged accounts receivable report. This report has a line for each account and shows the age ranges for all the dollars that are due from that account. Each report column in the report holds a range of ages. With an accounts receivable report, for example, the following inclusive age ranges are often used: 0-29 days old (or current), 30- 59 days old, 60-89 days old, 90-120 days old, and over 120 days old. Blanks are printed wherever there are no dollars due in that age range. BY MONTH TYPE OF REPORT: Here the decision about which report column to use is based on the month value in a date column. Here there is no comparison to another date as there is in the aging type of report. A sales report by part by calendar month is a good example of this type of report. This report is a report with a line for each part number and 14 columns. The 14 columns include a column for the part number, one for each month of the year, and the one for total for the year. The total quantity for each month (for each part number) is added up and printed in the appropriate position in the report. If no parts for a particular part number are sold in a particular month, then that "cell" of the report is left blank. RANGES TYPE OF REPORT: Here the report writer looks at a database column that contains numbers and compares it to ranges that are located in each of the report columns. The decision about where to print the value depends on a comparison between the value and the applicable range(s). Often a range report will print a letter in the report columns to indicate what range ([H]igh, [L]ow, or [N]ormal) the number falls under for that particular report column The range type of report is often used in medical, statistical, or scientific applications. A good example is a chemical scan report. This report has a different normal range for each of the chemicals. Each chemical has its own report column on the top of the page and each line in the report represents a different sample. Each chemical has its own range of normal values. The report writer prints an H in the cell of the report if the numeric value for that sample is in the high range for that chemical, an L if the value is low, and an N if the value is in the normal range. If the sample has not been tested for a particular chemical, a blank is printed. MICRORIM ONLINE November 1986 ------------------------------- Page 17 SOLUTION: """""""" This is how a multi-across labels report looks: GEORGE MEAKER ALANA SMITH JOSEPH ANDERSON JIMS GARAGE ANDERSONS APPLE BERRY PRODUCTS 302 PAULINE P.O. BOX 17026 4000 CONSTITUTION ANN ARBOR MI 48104 SEATTLE WA 98107 REDMOND WA 98073 JIM WESTERFIELD MARY APPLE MELANIE SODDERDON MEYERSON FABRICS EDUCATIONAL PRODUCTS JONES AND SMITH 123 NORTH MAIN ST 345 STEVENS DR. 567 LOST LANE SEATTLE WA 98107 JONESTOWN IN 47356 LEMA TX 76999 JOHN MINKLE JOHN SMITH MARY JOHNSON THE MEDICINE SHOP COMPUTER CONSULTANTS MARIA APPLEBERRY, MD 123 SMITH 12 MIDDLE STREET 123 MAIN ST LONE RANGE NM 88456 NETTLES NJ 07045 NEW YORK NY 14483 Use this kind of report to print repeating groups of information across the page. The example presented here uses three groups and because labels are being printed it is called a three-across labels report. To produce this report follow these steps: STEP ONE: Define the database. This example uses a table named PEOPLE with these columns: # Name Type Length Key Expression 1 FIRST TEXT 10 characters 2 LAST TEXT 14 characters 3 COMPANY TEXT 24 characters 4 ADDR TEXT 24 characters 5 CITY TEXT 15 characters 6 STATE TEXT 2 characters 7 ZIP TEXT 10 characters MICRORIM ONLINE November 1986 ------------------------------- Page 18 Next, load the table with data. For this example, the table has this data in it: FIRST LAST COMPANY ADDR ST ZIP ------- ----------- -------------------- ----------------- -- ----- GEORGE MEAKER JIMS GARAGE 302 PAULINE MI 48104 ALANA SMITH ANDERSONS P.O. BOX 17026 WA 98107 JOSEPH ANDERSON APPLE BERRY PRODUCTS 4000 CONSTITUTION WA 98073 JIM WESTERFIELD MEYERSON FABRICS 123 NORTH MAIN ST WA 98107 MARY APPLE EDUCATIONAL PRODUCTS 345 STEVENS DR. IN 47356 MELANIE SODDERDON JONES AND SMITH 567 LOST LANE TX 76999 JOHN MINKLE THE MEDICINE SHOP 123 SMITH NM 88456 JOHN SMITH COMPUTER CONSULTANTS 12 MIDDLE STREET NJ 07045 MARY JOHNSON MARIA APPLEBERRY, MD 123 MAIN ST NY 14483 STEP TWO: Initialize variables Initialize all the variables you will be using in the report. This means a variable for every element in each of the three groups plus two other variables VBREAKER and VCOUNTER which will be used to accomplish the "three-across" effect. In this example the following list of commands will do the necessary initializations: CLEAR ALL VARIABLES SET VAR V1NAME TEXT *( Notice that each variables is repeated for ) SET VAR V2NAME TEXT *( the number "across" you will be printing. ) SET VAR V3NAME TEXT *( Name is concatenation of FIRST & LAST. ) SET VAR V1COMP TEXT SET VAR V2COMP TEXT SET VAR V3COMP TEXT SET VAR V1ADDR TEXT SET VAR V2ADDR TEXT SET VAR V3ADDR TEXT SET VAR V1CSZ TEXT *( CSZ is concatenation of CITY, STATE, ZIP ) SET VAR V2CSZ TEXT SET VAR V3CSZ TEXT SET VAR VBREAKER INTEGER SET VAR VCOUNTER INTEGER This same set of initializations should also be done before running the report. STEP THREE: Define the report using Reports EXPRESS First, choose Expressions from the Reports Definition Menu and set up the expressions listed below. Later VBREAKER will be set up as a break variable in the Configuration step of report definition. It is the expressions which control the process. In this example, three report columns are being printed so the number three is used in the denominator of the VBREAKER expression. In this way, because VBREAKER is an integer, all fractional portions of the division will are truncated and ignored. The report therefore only sees a change in value for VBREAKER every third row. Later, in the Edit step of report definition, all variables being printed on the report will be located MICRORIM ONLINE November 1986 ------------------------------- Page 19 in a break footer section. This causes data to be printed only at break time. Therefore, three rows of data are read in before being printed. If your report needs four report columns, change the three in the denominator expression 13 to four and between expression 12 and expression 13 add a set of variables for the fourth column. 1: TEXT : V1NAME = .V2NAME 2: TEXT : V1COMP = .V2COMP 3: TEXT : V1ADDR = .V2ADDR 4: TEXT : V1CSZ = .V2CSZ 5: TEXT : V2NAME = .V3NAME 6: TEXT : V2COMP = .V3COMP 7: TEXT : V2ADDR = .V3ADDR 8: TEXT : V2CSZ = .V3CSZ 9: TEXT : V3NAME = ('FIRST' & 'LAST') 10: TEXT : V3COMP = 'COMPANY' 11: TEXT : V3ADDR = 'ADDR' 12: TEXT : V3CSZ = ('CITY' & 'STATE' & 'ZIP') 13: INTEGER : VBREAKER = (.VCOUNTER / 3) 14: INTEGER : VCOUNTER = (.VCOUNTER + 1) The order of the expressions variables in the list is crucial. The expressions are processed from top to bottom. The first of three rows of data is read into the V3 set of variables in expressions 9 through 12. The other variables remain null because there was no value in the variables to the right of the equal sign when the expressions were being processed. The second time through (with the second row of data) the V2 variables in expressions 5 through 8 save the current values of the V3 variables (from the first row) and then the second row is read into the V3 variables. Finally, when the third row is read in, the V1 variables in expressions 1 through 4 take on the values from the first row, the V2 variables get the values from the second row, and the third row is read into the V3 variables. After the third row, VBREAKER changes value because there is no remainder in the division that is done in expression 13. When VBREAKER changes value the report writer interprets that as a break and the set of three is printed. After setting up the expressions you need, press the [ESC] key to return to the Reports Definition Menu and choose Configure. Set the lines per page to zero. Give VBREAKER as the break variable and press [Y] to add VBREAKER and VCOUNTER to the reset list. To be completely safe, you might want to reset ALL the variables but remember that you can only have a maximum of 20 variables in the reset list. By resetting all the variables, you will insure that no label is double MICRORIM ONLINE November 1986 ------------------------------- Page 20 printed. When finished, the top of the configure menu will look like this: Lines Per Page .................: 0 Remove Initial Carriage Return..: [NO ] Manual Break Reset .............: [NO ] Page Footer Line Number.........: 0 BREAKPOINTS FORM FEEDS Break Variable Header Footer Column Reset Before After Before After -------- ------ ------ ------ ------ ------ Report [NO ] [NO ] [NO ] [NO ] Page [NO ] Break1 VBREAKER [YES] [NO ] Break2 [None] [NO ] [NO ] Finally, press the [ESC] key to get back to the Reports Definition Menu and choose Edit. Mark the section F1 to make it a break footer section. In this label example, you will want six F1 lines. Now, press the [F9] key to turn EXPAND off and locate all the V1, V2, and V3 variables in their appropriate report columns. When finished the report will look like this: F1S {v1name} E S {v2name} E S {v3name} E F1S {v1comp} E S {v2comp} E S {v3comp} E F1S {v1addr} E S {v2addr} E S {v3addr} E F1S {v1csz} E S {v2csz} E S {v3csz} E F1 F1 STEP FOUR: Print the report Now you can print the report by running a command file that clears all the variables used in the report, initializes all variables, sets null to a blank, prints the report (with any SORTED BY or WHERE clause that you need), and then sets null back to -0-. In this way you can print data in columns across the page. This method, with minor modification, also works with R:BASE 5000. The only modification is in the number of expressions and the method used to set up the report. With R:BASE 5000, you will need more variables because only one operator per expression is allowed. You will also need to load the first and last names into variables before concatenating them to form the name variables. MICRORIM ONLINE November 1986 ------------------------------- Page 21